package com.tham.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import oracle.jdbc.OracleTypes;
import utiles.AppProperties;
import utiles.ConexionPool;

import com.portal.dto.ComboDTO;
import com.tham.dto.AgendaDTO;
import com.tham.vo.RangoAgendaVO;

public class AgendaDAO {
	private static Logger log = Logger.getLogger(AgendaDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	
	public List<ComboDTO> getSemana() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	

		List<ComboDTO> listCombo = new ArrayList<ComboDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getSemana(?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.registerOutParameter(1, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(1);

			while (rs.next()) {
				ComboDTO comDTO = new ComboDTO();
				comDTO.setId(rs.getString(1));
				comDTO.setDesc(rs.getString(2));	
				listCombo.add(comDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getSemana SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listCombo;

	}	

	// Id Prestacion
	public List<ComboDTO> getPeriodo(String tipo, String vId) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    					    

		List<ComboDTO> listCombo = new ArrayList<ComboDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getPeriodo(?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, tipo);
			stmt.setString(2, vId);
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(3);

			while (rs.next()) {
				ComboDTO comDTO = new ComboDTO();
				comDTO.setId(rs.getString(1));
				comDTO.setDesc(rs.getString(2));	
				listCombo.add(comDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getPeriodo SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listCombo;

	}	

	public List<ComboDTO> getMotivoBloqueo() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    						    

		List<ComboDTO> listCombo = new ArrayList<ComboDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getMotivoBloqueo(?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.registerOutParameter(1, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(1);

			while (rs.next()) {
				ComboDTO comDTO = new ComboDTO();
				comDTO.setId(rs.getString(1));
				comDTO.setDesc(rs.getString(2));	
				listCombo.add(comDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getMotivoBloqueo SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listCombo;

	}	

	public List<AgendaDTO> getValidaAgenda(String vLu, String vMa, String vMi, String vJu, String vVi, String vSa, String vFchIni, String vFchFin, String vHoraIni, String vHoraFin, String vIdPro, String xIdSeman, String xIdBox) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    					    

		List<AgendaDTO> listCombo = new ArrayList<AgendaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getValidaAgenda(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, vLu);
			stmt.setString(2, vMa);
			stmt.setString(3, vMi);
			stmt.setString(4, vJu);
			stmt.setString(5, vVi);
			stmt.setString(6, vSa);
			stmt.setString(7, vFchIni);
			stmt.setString(8, vFchFin);
			stmt.setString(9, vHoraIni);
			stmt.setString(10, vHoraFin);
			stmt.setString(11, vIdPro);
			stmt.setString(12, xIdSeman);
			stmt.setString(13, xIdBox);
			stmt.registerOutParameter(14, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(14);

			while (rs.next()) {
				AgendaDTO comDTO = new AgendaDTO();
				comDTO.setCantHrsTotal(rs.getString(1));
				comDTO.setCantHrsDisp(rs.getString(2));
				comDTO.setCantHrsOcup(rs.getString(3));
				comDTO.setCantHrsBloq(rs.getString(4));
				comDTO.setCantHrsBox(rs.getString(5));
				//System.out.println("box: "+rs.getString(5));
				//log.error(rs.getString(6));
				listCombo.add(comDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getValidaAgenda SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listCombo;

	}
	
	public List<AgendaDTO> setRangosAgenda(RangoAgendaVO raVO) throws SQLException, IOException{
		List<AgendaDTO> listAgenda = new ArrayList<AgendaDTO>();
			int idRangoAgenda = 0;
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    				    
					try {
						//String query = "{ call "+pkgVar+".Pkg_tho_agenda.setRangosAgenda(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";				//String query = "{ call "+pkgVar+".Pkg_tho_rangos_agenda.setRangosAgenda(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";

						String query = "{ call "+pkgVar+".Pkg_tho_agenda.setRangosAgenda(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";				
						CallableStatement  stmt = conn.prepareCall(query);
						stmt.setString(1, raVO.getDiaLu());
						stmt.setString(2, raVO.getDiaMa());
						stmt.setString(3, raVO.getDiaMi());
						stmt.setString(4, raVO.getDiaJu());
						stmt.setString(5, raVO.getDiaVi());
						stmt.setString(6, raVO.getDiaSa());
						stmt.setString(7, raVO.getFechaIni());
						stmt.setString(8, raVO.getFechaFin());
						stmt.setString(9, raVO.getHoraIni());
						stmt.setString(10, raVO.getHoraFin());
						stmt.setString(11, raVO.getIdProfesional());
						stmt.setString(12, raVO.getIdSemana());
						stmt.setString(13, raVO.getIdPeriodo());
						stmt.setString(14, raVO.getTipo());
						stmt.setString(15, raVO.getIdSede());
						stmt.setString(16, raVO.getIdBox());
						
						stmt.registerOutParameter(17, OracleTypes.CURSOR);
						stmt.execute();
						ResultSet rs = (ResultSet)stmt.getObject(17);            
						while (rs.next()) {
							AgendaDTO agendDTO = new AgendaDTO();
							/*agendDTO.setIdRangosAgenda(rs.getString(1));
							idRangoAgenda = rs.getInt(1);
							agendDTO.setCantHrsTotal(rs.getString(2));
							agendDTO.setCantHrsDisp(rs.getString(3));
							agendDTO.setCantHrsOcup(rs.getString(4));
							agendDTO.setCantHrsBloq(rs.getString(5));
							agendDTO.setCantHrsDesB(rs.getString(6));*/
							agendDTO.setAgCreadas(rs.getString(1));
							agendDTO.setCantHrsTotal(rs.getString(2));
							agendDTO.setRetorno(rs.getString(3));
							agendDTO.setCantHrsDesB(rs.getString(4));
							listAgenda.add(agendDTO);			
						}
						rs.close();
						stmt.close();
					} catch (SQLException ex) {  
						//log.error("Error RangosAgendaDAO.setRangosAgenda SQLException: " +ex.getMessage());
						log.error("Error AgendaDAO.setRangosAgenda SQLException: " +ex.getMessage());
						System.out.println(ex.getMessage());				
					} finally {
						//ConexionPool.desconectaPool(conn);
						conn.close();
						conn = null;
						System.out.println("Se Crea Rango Agenda ID:"+idRangoAgenda);
						log.info("Se Crea Rango Agenda ID:"+idRangoAgenda);						
					}						
				return listAgenda;
		}

	
	public List<AgendaDTO> getAgenda(String vIdPro) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    					    

		List<AgendaDTO> listCombo = new ArrayList<AgendaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getAgenda(?, ?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, vIdPro);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);

			while (rs.next()) {
				AgendaDTO comDTO = new AgendaDTO();
				comDTO.setDia(rs.getString(1));
				comDTO.setFecInicio(rs.getString(2));
				comDTO.setFecFin(rs.getString(3));
				comDTO.setSemana(rs.getString(4));
				comDTO.setTipoHora(rs.getString(5));
				comDTO.setHoraInicio(rs.getString(6));
				comDTO.setHoraTermino(rs.getString(7));
				comDTO.setCantHrsDisp(rs.getString(8));
				comDTO.setCantHrsBloq(rs.getString(9));
				comDTO.setCantHrsTotal(rs.getString(10));
				comDTO.setIdRangosAgenda(rs.getString(11));
				comDTO.setSedeSigla(rs.getString(12));
				comDTO.setDescBox(rs.getString(13));
				listCombo.add(comDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getAgenda SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listCombo;
	}
	
	public List<AgendaDTO> getValidadBloqueo(RangoAgendaVO raVO) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    						    

		List<AgendaDTO> listAgenda = new ArrayList<AgendaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getValidadBloqueo(?,?,?,?,?,?,?,?,?,?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, raVO.getDiaLu());
			stmt.setString(2, raVO.getDiaMa());
			stmt.setString(3, raVO.getDiaMi());
			stmt.setString(4, raVO.getDiaJu());
			stmt.setString(5, raVO.getDiaVi());
			stmt.setString(6, raVO.getDiaSa());
			stmt.setString(7, raVO.getFechaIni());
			stmt.setString(8, raVO.getFechaFin());
			stmt.setString(9, raVO.getHoraIni());
			stmt.setString(10, raVO.getHoraFin());
			stmt.setString(11, raVO.getIdProfesional());
			stmt.setString(12, raVO.getTipo()); //Bloqueo o Desbloqueo
			stmt.registerOutParameter(13, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(13);            
			while (rs.next()) {
				AgendaDTO agendDTO = new AgendaDTO();
				agendDTO.setCantHrsBloq(rs.getString(1));
				listAgenda.add(agendDTO);			
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getValidadBloqueo SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listAgenda;
	}

	public List<AgendaDTO> setBloqueo(RangoAgendaVO raVO) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    					    

		List<AgendaDTO> listAgenda = new ArrayList<AgendaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.setBloqueo(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, raVO.getDiaLu());
			stmt.setString(2, raVO.getDiaMa());
			stmt.setString(3, raVO.getDiaMi());
			stmt.setString(4, raVO.getDiaJu());
			stmt.setString(5, raVO.getDiaVi());
			stmt.setString(6, raVO.getDiaSa());
			stmt.setString(7, raVO.getFechaIni());
			stmt.setString(8, raVO.getFechaFin());
			stmt.setString(9, raVO.getHoraIni());
			stmt.setString(10, raVO.getHoraFin());
			stmt.setString(11, raVO.getIdMotivo());
			stmt.setString(12, raVO.getCantidad());
			stmt.setString(13, raVO.getIdUser());
			stmt.setString(14, raVO.getIdProfesional());
			stmt.setString(15, raVO.getTipo());  //Bloqueo o Desbloqueo
			stmt.registerOutParameter(16, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(16);            
			while (rs.next()) {
				AgendaDTO agendDTO = new AgendaDTO();
				agendDTO.setCantHrsBloq(rs.getString(1));
				listAgenda.add(agendDTO);			
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.setBloqueo SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listAgenda;
	}

	public List<AgendaDTO> setDeleteAgenda(String idRango) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    					    

		List<AgendaDTO> listAgenda = new ArrayList<AgendaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.setDeleteAgenda(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idRango);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);            
			while (rs.next()) {
				AgendaDTO agendDTO = new AgendaDTO();
				agendDTO.setIdRangosAgenda(rs.getString(1));
				agendDTO.setObservacion(rs.getString(2));
				listAgenda.add(agendDTO);			
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.setBloqueo SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listAgenda;
	}

	public List<AgendaDTO> getBloqueos(RangoAgendaVO raVO) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    						    

		List<AgendaDTO> listBloqueo = new ArrayList<AgendaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getBloqueos(?,?,?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);

			stmt.setString(1, raVO.getFechaIni());
			stmt.setString(2, raVO.getFechaFin());
			stmt.setString(3, raVO.getFecha());
			stmt.setString(4, raVO.getIdProfesional());
			stmt.setString(5, raVO.getIdServicio());
			stmt.registerOutParameter(6, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(6);            
			while (rs.next()) {
				AgendaDTO bloq = new AgendaDTO();
				bloq.setMotivoBloqueo(rs.getString(1));
				bloq.setFechaBloqueo(rs.getString(2));
				bloq.setMedico(rs.getString(3));
				bloq.setFechaCita(rs.getString(4));
				bloq.setCantHrsTotal(rs.getString(5));
				listBloqueo.add(bloq);			
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getBloqueos SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listBloqueo;
	}

	public List<AgendaDTO> getCalendarioBox(String fecha) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();

		List<AgendaDTO> listBloqueo = new ArrayList<AgendaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.getBoxCalendario(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);

			stmt.setString(1, fecha);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);            
			while (rs.next()) {
				AgendaDTO bloq = new AgendaDTO();
				bloq.setDescBox(rs.getString(1));
				bloq.setDia(rs.getString(2));
				bloq.setMedico(rs.getString(3));
				listBloqueo.add(bloq);			
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.getCalendarioBox SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listBloqueo;
	}
	
	
	public List<AgendaDTO> setHolter(String dias, String horaDesde, String horaHasta, String idProf, String fechaDesasignar) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
		List<AgendaDTO> listCombo = new ArrayList<AgendaDTO>();
		
		try {
			String query = "{ call "+pkgVar+".PKG_THO_AGENDA.setHolter(?, ?, ?, ?, ?, ?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, dias);
			stmt.setString(2, horaDesde);
			stmt.setString(3, horaHasta);
			stmt.setString(4, idProf);
			stmt.setString(5, fechaDesasignar);
			System.out.println(fechaDesasignar);
			stmt.registerOutParameter(6, OracleTypes.NUMBER);
			stmt.execute();
			AgendaDTO medDTO = new AgendaDTO();
			medDTO.setCantHrsTotal(stmt.getString(6));
			listCombo.add(medDTO);
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error AgendaDAO.setHolter SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listCombo;

	}			
	
}
